--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_PRO_ORG_LOAB.HQL
--    Functions : 表32：贷款余额分机构类型分产品统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_PRO_ORG_LOAB PARTITION (DATA_DATE = '#V_DATA_DATE#')
    SELECT
     AREA.AREA_CODE_4                                                                                   AS FIN_ORG_DIST
    ,YE.CCY                                                                                             AS CCY
    ,ORG.ORG_CODE                                                                                       AS ORG_TYP          -- 机构类型
    ,ORG.ORG_DSCR                                                                                       AS ORG_DESC         -- 机构类型描述
    ,CASE -- 普通贷款
          WHEN YE.BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219') THEN 'A1'
          WHEN YE.BUSINESS_TYPE IN ('F02201','F02202','F02203') THEN 'A2'
          WHEN YE.BUSINESS_TYPE='F023' THEN 'A3'
          -- 贸易融资
          WHEN YE.BUSINESS_TYPE='F081' THEN 'B1'
          WHEN YE.BUSINESS_TYPE='F082' THEN 'B2'
          -- 融资租赁
           WHEN YE.BUSINESS_TYPE='F09' THEN 'C'
     END                                                                                                AS BUSINESS_TYPE    -- 贷款产品类型
    ,CASE -- 普通贷款
          WHEN YE.BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219') THEN '消费贷款'
          WHEN YE.BUSINESS_TYPE IN ('F02201','F02202','F02203') THEN '经营贷款'
          WHEN YE.BUSINESS_TYPE='F023' THEN '固定资产贷款'
          -- 贸易融资
          WHEN YE.BUSINESS_TYPE='F081' THEN '国际贸易融资'
          WHEN YE.BUSINESS_TYPE='F082' THEN '国内贸易融资'
          -- 融资租赁
          WHEN YE.BUSINESS_TYPE='F09' THEN '融资租赁'
     END                                                                                                AS TYPE_DESC        -- 贷款产品类型描述
    ,SUM(COALESCE(YE.ACTUAL_BAL,0)/100000000                                                            AS ACTUAL_BAL       -- 贷款余额
    ,SUM(COALESCE(YE.BAL_LM,0))/100000000                                                               AS BAL_LM           -- 余额上期末
    ,SUM(COALESCE(YE.BAL_YF,0))/100000000                                                               AS BAL_YF           -- 余额年初 0101
    ,SUM(COALESCE(YE.ACTUAL_BAL,0) - COALESCE(YE.BAL_LC,0))/100000000                                   AS BAL_GROW         -- 同比增长
    ,SUM(COALESCE(YE.WSUM_BAL,0))/100000000                                                             AS WSUM_BAL         -- 余额加权值
    ,SUM(COALESCE(YE.WBAL_LM,0))/100000000                                                              AS WBAL_LM          -- 余额加权值上期末
    ,SUM(COALESCE(YE.WBAL_YF,0))/100000000                                                              AS WBAL_YF          -- 余额加权值年初
    ,SUM(COALESCE(YE.WBAL_LC,0))/100000000                                                              AS WBAL_LC          -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219','F02201','F02202','F02203','F023','F081','F082','F09')
          AND DATA_DATE='#V_DATA_DATE#') YE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON YE.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 关联机构表
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON YE.ORG_TYPE=ORG.ORG_CODE
    GROUP BY  AREA.AREA_CODE_4
             ,YE.CCY
             ,ORG.ORG_CODE
             ,ORG.ORG_DSCR
             ,CASE -- 普通贷款
                   WHEN YE.BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219') THEN 'A1'
                   WHEN YE.BUSINESS_TYPE IN ('F02201','F02202','F02203') THEN 'A2'
                   WHEN YE.BUSINESS_TYPE='F023' THEN 'A3'
                   -- 贸易融资
                   WHEN YE.BUSINESS_TYPE='F081' THEN 'B1'
                   WHEN YE.BUSINESS_TYPE='F082' THEN 'B2'
                   -- 融资租赁
                    WHEN YE.BUSINESS_TYPE='F09' THEN 'C'
              END
             ,CASE -- 普通贷款
                   WHEN YE.BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219') THEN '消费贷款'
                   WHEN YE.BUSINESS_TYPE IN ('F02201','F02202','F02203') THEN '经营贷款'
                   WHEN YE.BUSINESS_TYPE='F023' THEN '固定资产贷款'
                   -- 贸易融资
                   WHEN YE.BUSINESS_TYPE='F081' THEN '国际贸易融资'
                   WHEN YE.BUSINESS_TYPE='F082' THEN '国内贸易融资'
                   -- 融资租赁
                   WHEN YE.BUSINESS_TYPE='F09' THEN '融资租赁'
              END

    UNION ALL

    -- 合计
    SELECT
     AREA.AREA_CODE_4                                                                                            AS FIN_ORG_DIST
    ,YE.CCY                                                                                                      AS CCY
    ,ORG.ORG_CODE                                                                                                AS ORG_TYP          -- 机构类型
    ,ORG.ORG_DSCR                                                                                                AS ORG_DESC         -- 机构类型描述
    ,'Z'                                                                                                         AS BUSINESS_TYPE    -- 贷款产品类型
    ,'合计'                                                                                                      AS TYPE_DESC        -- 贷款产品类型描述
    ,SUM(COALESCE(CASE WHEN YE.DATA_DATE='#V_DATA_DATE#' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000             AS ACTUAL_BAL       -- 贷款余额
    ,SUM(COALESCE(CASE WHEN YE.DATA_DATE='#V_PREV_MONTH_DATE#' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000       AS BAL_LM           -- 余额上期末
    ,SUM(COALESCE(CASE WHEN YE.DATA_DATE='#V_YEAR_FIRST_DATE#' THEN YE.ACTUAL_BAL ELSE 0 END,0))/100000000       AS BAL_YF           -- 余额年初 0101
    ,SUM((COALESCE(CASE WHEN YE.DATA_DATE='#V_DATA_DATE#' THEN YE.ACTUAL_BAL ELSE 0 END,0))
     -
     (COALESCE(CASE WHEN YE.DATA_DATE='#V_PREV_YEAR_DATE#' THEN YE.ACTUAL_BAL ELSE 0 END,0)))/100000000          AS BAL_GROW         -- 同比增长
    ,SUM(COALESCE(CASE WHEN YE.DATA_DATE='#V_DATA_DATE#' THEN YE.WSUM_BAL ELSE 0 END,0))/100000000               AS WSUM_BAL         -- 余额加权值
    ,SUM(COALESCE(CASE WHEN YE.DATA_DATE='#V_PREV_MONTH_DATE#' THEN YE.WSUM_BAL ELSE 0 END,0))/100000000         AS WBAL_LM          -- 余额加权值上期末
    ,SUM(COALESCE(CASE WHEN YE.DATA_DATE='#V_YEAR_FIRST_DATE#' THEN YE.WSUM_BAL ELSE 0 END,0))/100000000         AS WBAL_YF          -- 余额加权值年初
    ,SUM(COALESCE(CASE WHEN YE.DATA_DATE='#V_PREV_YEAR_DATE#' THEN YE.WSUM_BAL ELSE 0 END,0))/100000000          AS WBAL_LC          -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE DATA_DATE='#V_DATA_DATE#') YE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON YE.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 关联机构表
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON YE.ORG_TYPE=ORG.ORG_CODE
    GROUP BY  AREA.AREA_CODE_4
             ,YE.CCY
             ,ORG.ORG_CODE
             ,ORG.ORG_DSCR;